﻿#region using Directive
using System;
using System.Collections.Generic;
using System.Configuration;
using System.IO;
using System.Net.Mail;
using System.Resources;
using System.Text;
using System.Threading;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;

#endregion

/*********************************************************************
 * 关于代码的说明:
 * 修改变量名的时候不需要再改resource里面的代码
 * 
 * 
 * 
 * 
 * 
 * 
 * 
 * 
 * 
 * 
 * 
 * *********************************************************************/

namespace MSBIHelper
{
   class PackageHelper
    {
        //const string 
        #region Properties
        const string scriptlanguage = "Microsoft Visual C# 2008";
        //msbihelperehobj_eventname_subject
        const string OnErrorEventHandlerTaskName = "MSBIHelper_UpdateErrorinfoByEvan";
        const string OnErrorInsertErrorInfoName = "MSBIHelper_LogErrorInfo";
        const string OnWarningInfoName = "MSBIHelper_LogWarningInfo";
        const string OnPreExecuteEventHandlerTaskName = "MSBIHelper_AddPkgInfo";
        const string OnPostExecuteEventHandlerScriptTaskName = "MSBIHelper_UpdatePkgInfo";
        const string OnPostExecuteEventHandlerTaskName = "MSBIHelper_SendEmailNotificationByEvan";

        //varmsbihelper(2)_type_desc_count
        const string pkg_error_var_name = "MSBIHelper2_DictError";
        const string sMSBIHelper_Conn = "MSBIHelper_Log_Conn";
        const string DemoPkgName = "SSIS_Demo_Pkg";
        const string MyResourceName1 = "UpdateErrorInfo";
        const string MyResourceName2 = "SendMailNotification";
        const string MyResourceName4 = "logPreExecuteScript";
        const string MyResourceName5 = "logPostExecutescriptTemplate";
        const string MyResourceName6 = "OnPostExecuteAudit";
        const string MyResourceName_LogErrorInfo = "LogErrorInfo";
        
        const string ESTVariableNamePrefix = "MSBIHelper_EST_RC_";
        const string NotificationVNamePrefix = "MSBIHelper2_";

        public bool IsDbLog = false;

        string csvfilepath = ConfigurationManager.AppSettings["MSBIHelper_CSVFilePath"].ToString();
        string genarateDailyFolder = ConfigurationManager.AppSettings["GenarateDailyFolder"].ToString();
        public Microsoft.SqlServer.Dts.Runtime.Application sqlPackageLoader 
            = new Microsoft.SqlServer.Dts.Runtime.Application();

        const string RowCountTaskName = "MSBIHelper_RowCountTask";
        private StringBuilder sbscripts = new StringBuilder();
        private StringBuilder sbSTMT = new StringBuilder();// used for Execute Sql Task
        private StringBuilder sbProcessInfo = new StringBuilder();
        private struct sEventType
        {
            public static string OnWarning = "OnWarning";
            public static string OnError = "OnError";
            public static string OnPostExecute = "OnPostExecute";
            public static string OnPreExecute = "OnPreExecute";
        }
        public string DemoPackageName { get { return DemoPkgName; } }
        public string RowCountVariableNamePrefix { get { return "MSBIHelper_RowCount_"; } }
        public string ProcessInfo { get {return sbProcessInfo.ToString();}}
        public string GetStringFromMyResource(string resourceName)
        {
            string rst = string.Empty;
                ResourceManager rm = new ResourceManager(typeof(MyResource));
                rst = rm.GetString(resourceName);
            return rst;
        }
        #endregion

        #region CreateDemoPkg
        public void CreateDemoPkg()
        {
            Package p = new Package();
            p.Name = DemoPkgName;
            p.ProtectionLevel = DTSProtectionLevel.DontSaveSensitive;

            // add pacakge variable
            if (!p.Variables.Contains(pkg_error_var_name))
             p.Variables.Add(pkg_error_var_name, false, string.Empty,new object());
　
            //Add onError eventHandler
            AddOnErrorEventHandler(ref p);
            //Add postExecute event handler
            AddOnPostExecuteEventHandler(ref p);
            
            CreateSourceFile();
            AddConnectionManagers(p);
            //Add DataFlow Task
            AddDataFlowTask(p);

            sqlPackageLoader.SaveToXml(p.Name + ".dtsx", p, null);
        
        }
        #endregion

        #region Common functions

        private void AddScriptTask(DtsEventHandler eh, string taskname, string projectName, string codeText)
        {
            Executable e = eh.Executables.Add("STOCK:ScriptTask");
            TaskHost myst = e as TaskHost;
            myst.Name = taskname;
            ScriptTask st = myst.InnerObject as ScriptTask;
            try
            {
                string guid = Guid.NewGuid().ToString().Replace("-", "");
                st.ScriptProjectName = "ST_" + guid ;
                st.ScriptLanguage = scriptlanguage;

                st.ScriptingEngine.InitNewScript("CSharp", st.ScriptProjectName, ".csproj");
                st.ScriptingEngine.ShowDesigner(false);
                st.ScriptingEngine.AddCodeFile("ScriptMain.cs", codeText.Replace("*ProjectName*",guid));
                if (!st.ScriptingEngine.Build())
                {
                    sbProcessInfo.AppendLine("ErrorOccure");
                    sbProcessInfo.AppendLine("build script task fail, package :" + eh.Parent.Name + " event handler:" + eh.Name);
                    return;
                }
                st.ScriptingEngine.SaveScriptToStorage();
            }
            catch (Exception eX)
            {
                sbProcessInfo.AppendLine("ErrorOccure");
                sbProcessInfo.AppendLine("Exception in package :" + eh.Parent.Name);
                sbProcessInfo.AppendLine(eX.Message + Environment.NewLine + eX.StackTrace);
            }
            finally
            {
                st.ScriptingEngine.CloseIDE(true);
            }
        }
 
        private DtsEventHandler getEventHandler(Package p, string eventHanelerName)
        {
            DtsEventHandler eh = null;
            if (p.EventHandlers.Contains(eventHanelerName))
                eh = p.EventHandlers[eventHanelerName]      as DtsEventHandler;
            else
                eh = p.EventHandlers.Add(eventHanelerName)  as DtsEventHandler;
            return eh;
        }

        private void RemoveScriptTask(Package p,DtsEventHandler eh,string taskname)
        {  
                List<string> scripttasklist = new List<string>();
                foreach (Executable item in eh.Executables)
                {
                    if ((item as TaskHost).Name.Contains(taskname))
                        scripttasklist.Add((item as TaskHost).Name);
                }
                foreach (string stname in scripttasklist)
                {
                    eh.Executables.Remove(stname);
                }
                if (eh.Executables.Count.Equals(0))
                    p.EventHandlers.Remove(eh.Name);
        }

        private int CountVariables(Package p, string pattern)
        {
            int count = 0;
            //Variables vars = null;
            //VariableDispenser variableDispenser = p.VariableDispenser;
            //variableDispenser.GetVariables(ref vars);
            foreach (var v in p.Variables)
            {
                if (v.Name.StartsWith(pattern))
                    count++;
            }
            //
            return count;
        }

        #endregion 

        #region AddNotifiction
        public void AddOnPostExecuteEventHandler(ref Package p,ParaObject _po =null)
        {
            DtsEventHandler ehOnPost = getEventHandler(p, sEventType.OnPostExecute);
            string CodeText = GetStringFromMyResource(MyResourceName2);
            AddNotificationVariables(ref p);
            if(_po==null)
            AddScriptTask(ehOnPost,OnPostExecuteEventHandlerTaskName,"stMSBIHELPERSSISDEMO_SendMail",CodeText);
            else
                AddScriptTask2(ehOnPost, OnPostExecuteEventHandlerTaskName, "stMSBIHELPERSSISDEMO_SendMail", CodeText,_po);
        }

        public void AddOnErrorEventHandler(ref Package p, bool removeformer = false,ParaObject _po=null)
        {
            DtsEventHandler ehOnError = getEventHandler(p, sEventType.OnError);
            //remove the former handler 
            if (removeformer && ehOnError.Executables.Count > 0)
            foreach (var item in ehOnError.Executables)
            {
                ehOnError.Executables.Remove(item);
            }
            if(_po == null)
            AddScriptTask(ehOnError,
                          OnErrorEventHandlerTaskName,
                          "stMSBIHELPERSSISDEMO_OnError", 
                          GetStringFromMyResource(MyResourceName1));
            else

                AddScriptTask2(ehOnError,
                              OnErrorEventHandlerTaskName,
                              "stMSBIHELPERSSISDEMO_OnError",
                              GetStringFromMyResource(MyResourceName1),_po);
        }

        public void addNotificationForOnePkg(string pkgpath,bool bremoveFormer=false)
        {
            Package p = sqlPackageLoader.LoadPackage(pkgpath, null);

            if (!p.Variables.Contains(pkg_error_var_name))
                p.Variables.Add(pkg_error_var_name, false, "User", new object());

            if (!p.EventHandlers.Contains(sEventType.OnError) ||
                !p.EventHandlers[sEventType.OnError].Executables.Contains(OnErrorEventHandlerTaskName))
                 AddOnErrorEventHandler(ref p, bremoveFormer);

            if (!p.EventHandlers.Contains(sEventType.OnPostExecute) ||
                !p.EventHandlers[sEventType.OnPostExecute].Executables.Contains(OnPostExecuteEventHandlerTaskName))
                AddOnPostExecuteEventHandler(ref p);

            sqlPackageLoader.SaveToXml(pkgpath, p, null);
        }

        private void AddNotificationVariables(ref Package p)
        {
            string SMTPServer = ConfigurationManager.AppSettings["SMTPServer"].ToString();
            string MailFrom = ConfigurationManager.AppSettings["SSISFrom"].ToString();
            string MailTo = ConfigurationManager.AppSettings["SSISTo"].ToString();
            if (!p.Variables.Contains(NotificationVNamePrefix + "MailServer"))
                p.Variables.Add("MSBIHelper2_MailServer", false, "User", SMTPServer);
            else p.Variables["MSBIHelper2_MailServer"].Value = SMTPServer;
            if (!p.Variables.Contains("MSBIHelper2_MailFrom"))
                p.Variables.Add("MSBIHelper2_MailFrom", false, "User", MailFrom);
            else p.Variables["MSBIHelper2_MailFrom"].Value = MailFrom;
            if (!p.Variables.Contains("MSBIHelper2_MailTo"))
                p.Variables.Add("MSBIHelper2_MailTo", false, "User", MailTo);
            else p.Variables["MSBIHelper2_MailTo"].Value = MailTo;
           
        }

        public void RemoveNotificationForOnePkg(string pkgpath)
        {
            // remove the EventHandler if the EventHandler Only have One task
            Package p = sqlPackageLoader.LoadPackage(pkgpath, null);
            if (p.EventHandlers.Contains(sEventType.OnError))
                if (p.EventHandlers[sEventType.OnError].Executables.Contains(OnErrorEventHandlerTaskName))
                {
                    p.EventHandlers[sEventType.OnError].Executables.Remove(OnErrorEventHandlerTaskName);
                    if (p.EventHandlers[sEventType.OnError].Executables.Count.Equals(0))
                        p.EventHandlers.Remove(sEventType.OnError);
                }

            if (p.EventHandlers.Contains(sEventType.OnPostExecute))
                if (p.EventHandlers[sEventType.OnPostExecute].Executables.Contains(OnPostExecuteEventHandlerTaskName))
                {
                    p.EventHandlers[sEventType.OnPostExecute].Executables.Remove(OnPostExecuteEventHandlerTaskName);
                    if (p.EventHandlers[sEventType.OnPostExecute].Executables.Count.Equals(0))
                        p.EventHandlers.Remove(sEventType.OnPostExecute);
                }
            RemoveVariables(p, NotificationVNamePrefix); 

            sqlPackageLoader.SaveToXml(pkgpath, p, null);
        }
        #endregion

        #region AddNotification Parallel
        public void addNotificationParallel(ParaObject _po)
        {
            try
            {
                Package p = sqlPackageLoader.LoadPackage(_po.pkgpath, null);

                if (!p.Variables.Contains(pkg_error_var_name))
                    p.Variables.Add(pkg_error_var_name, false, "User", new object());

                if (!p.EventHandlers.Contains(sEventType.OnError) ||
                    !p.EventHandlers[sEventType.OnError].Executables.Contains(OnErrorEventHandlerTaskName))
                    AddOnErrorEventHandler(ref p,_po.bRemoveFormer,_po);

                if (!p.EventHandlers.Contains(sEventType.OnPostExecute) ||
                    !p.EventHandlers[sEventType.OnPostExecute].Executables.Contains(OnPostExecuteEventHandlerTaskName))
                    AddOnPostExecuteEventHandler(ref p,_po);

                sqlPackageLoader.SaveToXml(_po.pkgpath, p, null);
                _po.sbProcessInfo.AppendFormat("Package :{0} has been added Notification Successfully !",p.Name);
            }
            catch (Exception eX)
            {
                _po.ErrorCount++;
                _po.sbProcessInfo.AppendLine("ErrorOccure");
                _po.sbProcessInfo.AppendLine("Exception in package :" + Path.GetFileName(_po.pkgpath));
                _po.sbProcessInfo.AppendLine(eX.Message + Environment.NewLine + eX.StackTrace);
                System.Diagnostics.Debug.WriteLine(
                    eX.Message + Environment.NewLine + eX.StackTrace
                    );
            }   
        }
        #endregion

        #region SendExceptionInfo
        public void SendMailToDeveloper(string content)
        {
            try
            {
                string sServer = "smtprelay-vip.houston.hp.com";
                string sFrom = "xiao-hua.yao@hp.com";
                string sTo = "xiao-hua.yao@hp.com";
                MailMessage oMessage = new MailMessage();
                oMessage.From = new MailAddress(sFrom);
                oMessage.IsBodyHtml = true;
                oMessage.Subject = "SSIS Helper Exception Information";
                oMessage.To.Add(new MailAddress(sTo));
                oMessage.Body = content;

                SmtpClient oSmtpClient = new SmtpClient(sServer);
                oSmtpClient.Send(oMessage);

            }
            catch (Exception EX) { sbProcessInfo.AppendLine(EX.Message + Environment.NewLine + EX.StackTrace); }
        }
        #endregion

        #region CheckList
        public string CheckList(string pkgpath)
        {
            //Package p = LoadPackage(pkgpath);
            //check DontSensitive
            //check logging onError
            //check some best practice
            StringBuilder result = new StringBuilder();
            try
            {
                Package p = sqlPackageLoader.LoadPackage(pkgpath, null);
                if (p.ProtectionLevel.Equals(DTSProtectionLevel.DontSaveSensitive))
                { result.AppendLine(string.Format("Package :{0} ProtectionLevel is DontSaveSensitive, Success!",p.Name)); }
                else
                { result.AppendLine(string.Format("Package :{0} ProtectionLevel is {1}, Fail!", p.Name, p.ProtectionLevel.ToString()));}
                if (p.LoggingMode.Equals(DTSLoggingMode.Disabled))
                {result.AppendLine(string.Format("Package :{0} Logging is {1}, Fail!",p.Name, p.LoggingMode.ToString()));}
                else
                {
                    result.AppendLine(string.Format("Package :{0} Logging is {1}, Success!",
                    p.Name, p.LoggingMode.ToString()));
                    if (p.LoggingOptions.SelectedLogProviders.Count.Equals(0))
                    {result.AppendLine(string.Format("Package :{0} SelectedLogProviders is 0, Fail!", p.Name));}
                    else
                    {
                        bool isOnErrorSelected = false;
                        foreach (var item in p.LoggingOptions.EventFilter)
                        {
                            if (item.Equals("OnError"))
                            {
                                isOnErrorSelected = true;
                                break;
                            }
                        }
                        if (isOnErrorSelected)
                            result.AppendLine(string.Format("Package :{0} OnError Event is Selected, Succcess!", p.Name));
                        else
                            result.AppendLine(string.Format("Package :{0} OnError Event is Not Selected, Fail!", p.Name));
                    }
                }
            }
            catch(Exception EX) { sbProcessInfo.AppendLine(EX.Message + Environment.NewLine + EX.StackTrace); }
            //catch (Exception ex)
            //{

            //    // if the package protect level is not DontSensitive it will cause one exception
            //    result.AppendLine(pkgpath + " " + ex.Message);
            //}            
            return result.ToString();
        }
        
        public string SSISBestPractice(string pkgpath)
        {
            //need to be implemented
            return string.Empty;
        }

        #endregion

        #region AddLogging
        /// <summary>
        /// Enable package level logging.
        /// </summary>
        public void AddLogging(string pkgpath)
        {
            try
            {
                Package p = sqlPackageLoader.LoadPackage(pkgpath, null);
                string logCMName = "MSBIHelper_Conn_LogFile";
                string logProviderName = "MSBIHelper SSIS log provider For Text File";
                p.LoggingMode = DTSLoggingMode.Enabled;
                string logFilePath = ConfigurationManager.AppSettings["logVariableValue"].ToString();
                string eventFilter = ConfigurationManager.AppSettings["logEventFilter"].ToString();

                if (!logFilePath.EndsWith("\\"))
                    logFilePath += "\\";
                // Add a file connection manager for the text log provider.
                ConnectionManager cm = null;
                if (!p.Connections.Contains(logCMName))
                {
                    cm = p.Connections.Add("FILE");
                    cm.Name = logCMName;
                    cm.Properties["FileUsageType"].SetValue(cm, DTSFileConnectionUsageType.CreateFile);
                    cm.SetExpression("ConnectionString", string.Format("\"{0}\"+ @[System::PackageName] +\"_\"+SUBSTRING(REPLACE(REPLACE(REPLACE( (DT_WSTR,50)GETDATE(), \"-\", \"\" ), \":\", \"\" ), \" \", \"\" ),1,8)+ \".txt\"", logFilePath.Replace(@"\", @"\\")));
                }
                else cm = p.Connections[logCMName] as ConnectionManager;
                // Add a LogProvider.
                if (!p.LogProviders.Contains(logProviderName))
                {
                    LogProvider provider = p.LogProviders.Add("DTS.LogProviderTextFile.2");
                    provider.ConfigString = cm.Name;
                    provider.Name = logProviderName;
                    p.LoggingOptions.SelectedLogProviders.Add(provider);
                    p.LoggingOptions.EventFilter = eventFilter.Split(',');
                }
                sqlPackageLoader.SaveToXml(pkgpath, p, null);
            }
            catch (Exception ex)
            {
                sbProcessInfo.AppendLine(ex.Message + Environment.NewLine + ex.StackTrace);
            }
        }
 
        public void UpdateLogging(string pkgpath)
        {
            string eventFilter = ConfigurationManager.AppSettings["logEventFilter"].ToString();
            try
            {
                Package p = sqlPackageLoader.LoadPackage(pkgpath, null);
                if(p.LoggingMode.Equals(DTSLoggingMode.Enabled))
                    p.LoggingOptions.EventFilter = eventFilter.Split(',');
                sbProcessInfo.AppendLine(
                    string.Format("Package Name:{0} has been finished adding logging!",p.Name)
                    );
            }
            catch (Exception ex)
            {
                sbProcessInfo.AppendLine(ex.Message + Environment.NewLine + ex.StackTrace);
            }
        }
 
        public void RemoveLogging(Package p)
        {
             string logCMName = "MSBIHelper_LogFile_Conn";
             string logProviderName = "MSBIHelper SSIS log provider For Text File";
 
             if(p.Connections.Contains(logCMName))
                p.Connections.Remove(logCMName);
             if (p.LoggingOptions.SelectedLogProviders.Contains(logProviderName))
                 p.LoggingOptions.SelectedLogProviders.Remove(logProviderName);
             if (p.LogProviders.Contains(logProviderName))
                 p.LogProviders.Remove(logProviderName);
             if(p.LoggingOptions.SelectedLogProviders.Count.Equals(0))
                p.LoggingMode = DTSLoggingMode.Disabled;
        }

        #endregion

        #region AddDataFlowTask
        /// <summary>
        /// Adds a DataFlow task to the Executables collection of the package.
        /// Retrieves the MainPipe object from the TaskHost and stores it in 
        /// the dataFlow member variable
        /// </summary>
        private void AddDataFlowTask(Package p)
        {
            TaskHost th = p.Executables.Add("STOCK:PipelineTask") as TaskHost;
            th.Name = "DataFlowTask";
            th.Description = "The DataFlow task in the DTSAuto sample.";
        }
        #endregion

        #region AddConnectionManagers
        /// <summary>
        /// Adds the OLEDB and FlatFile connection managers to the package.
        /// </summary>
        private void AddConnectionManagers(Package p)
        {
            // Add the OLEDB connection manager.
            ConnectionManager adventureWorks = p.Connections.Add("OLEDB");
            // Set stock properties.
            adventureWorks.Name = "MSBIHelper_OLEDB_Conn";
            adventureWorks.ConnectionString = @"Provider=SQLNCLI10;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorks2008R2;Data Source=(local);Auto Translate=False;";
            
            // Add the Destination connection manager.
            ConnectionManager cmflatFile = p.Connections.Add("FLATFILE");
            cmflatFile.Name = "MSBIHelper_FlatFile_Conn";
            // Set the stock properties.
            cmflatFile.Properties["ConnectionString"].SetValue(cmflatFile,"SourceFile1.txt");
            cmflatFile.Properties["Format"].SetValue(cmflatFile, "Delimited");
            cmflatFile.Properties["DataRowsToSkip"].SetValue(cmflatFile, 0);
            cmflatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmflatFile, false);
            cmflatFile.Properties["RowDelimiter"].SetValue(cmflatFile, "\r\n");
            cmflatFile.Properties["TextQualifier"].SetValue(cmflatFile, "\"");
            cmflatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(cmflatFile, true);
        }
        #endregion

        #region CreateSourceFile
        public void CreateSourceFile()
        {
            string SourceFileContent =  GetStringFromMyResource("SourceFile1");
            string SourceFilePath = "SourceFile1.txt";
            if (File.Exists(SourceFilePath))
                File.Delete(SourceFilePath);
            using (StreamWriter sw = File.CreateText(SourceFilePath))
            {
                sw.Write(SourceFileContent);
            }
        }
        #endregion

        #region AddExecuteSqlTask
        private void AddExecteSqlTask(Package p)
        {
            if (p.Connections.Contains("MSBIHelper_OLEDB_Conn"))
            {
                ConnectionManager ad = p.Connections["MSBIHelper_OLEDB_Conn"] as ConnectionManager;
                TaskHost th = p.Executables.Add("STOCK:SQLTask") as TaskHost;
                //ExecuteSQLTask task = th.InnerObject as ExecuteSQLTask;

                th.Properties["Connection"].SetValue(th, ad.ID);
                th.Properties["SqlStatementSource"].SetValue(th, "SELECT count(*) FROM sysobjects");
            }
 
        }
        #endregion
 
        #region Add AuditFramework

        private void LogPostExecute (Package p, bool isAddAudit)
        {
            DtsEventHandler ehOnPostExecute = getEventHandler(p, sEventType.OnPostExecute);
            //Remove Event Handler script task
            if (!isAddAudit)
            {
                RemoveScriptTask(p, ehOnPostExecute, OnPostExecuteEventHandlerScriptTaskName);
                RemoveVariables(p,sMSBIHelper_Conn);
                return;
            }
            //MSBIHelper_Log_Conn
            if (IsDbLog)
            {
                string connstr = ConfigurationManager.AppSettings["MSBIHELPER_LOG_CONN"].ToString();
                if (!p.Variables.Contains("MSBIHelper_Log_Conn")) p.Variables.Add("MSBIHelper_Log_Conn", false, "User", connstr);
            }
            else
            {
                if (!p.Variables.Contains("MSBIHelper_CSVFilePath")) p.Variables.Add("MSBIHelper_CSVFilePath", false, "User", csvfilepath);
                if (!p.Variables.Contains("MSBIHelper_GenarateDailyFolder")) p.Variables.Add("MSBIHelper_GenarateDailyFolder", false, "User", genarateDailyFolder);
            }

            sbscripts.AppendLine(sbSTMT.ToString());
            string codetext = GetStringFromMyResource(MyResourceName6).Replace("/*REPLACE YOUR CODE HERE PLEASE*/", sbscripts.ToString());
            if (IsDbLog)
            {
                codetext = codetext.Replace("//ReplaceHereUpdatePkgInfo", GetStringFromMyResource("db_PE_UpdatePkgInfo"));
                codetext = codetext.Replace("//ReplaceHereRowcountinfo", GetStringFromMyResource("db_PE_UpdateRowInfo"));
            }
            else
            {
                codetext = codetext.Replace("//ReplaceHereUpdatePkgInfo", GetStringFromMyResource("csv_PE_UpdatePkgInfo"));
                codetext = codetext.Replace("//ReplaceHereRowcountinfo", GetStringFromMyResource("csv_PE_UpdateRowInfo"));
            }
            AddScriptTask(ehOnPostExecute, OnPostExecuteEventHandlerScriptTaskName, "stMSBIHELPERSSISDEMO_UpdatePkgInfo", codetext);
        }
         
        private void LogPkgErrorInfo(Package p, bool isAddAudit)
        {
            //add one script task on error event handler
            DtsEventHandler ehOnError = getEventHandler(p, sEventType.OnError);
            //Remove 
            if (!isAddAudit && ehOnError != null)
            {
                RemoveScriptTask(p, ehOnError, OnErrorInsertErrorInfoName);
                return;
            }
            string codetext = GetStringFromMyResource(MyResourceName_LogErrorInfo);
            if (IsDbLog)
            { codetext = codetext.Replace("//ReplaceHere", GetStringFromMyResource("db_LogAlert")); }
            else
            { codetext = codetext.Replace("//ReplaceHere", GetStringFromMyResource("csv_LogAlert")); }
            codetext = codetext.Replace("@EventType@", "E");
            AddScriptTask(ehOnError, OnErrorInsertErrorInfoName,
                "stMSBIHELPERSSISDEMO_LogError",
                codetext);

        }
 
        private void LogPkgWarningInfo(Package p, bool isAddAudit)
        {
            //add one script task on warning event handler
            DtsEventHandler ehOnWarning = getEventHandler(p, sEventType.OnWarning);
            //Remove 
            if (!isAddAudit && ehOnWarning != null)
            {
                RemoveScriptTask(p, ehOnWarning, OnWarningInfoName);
                return;
            }
            string codetext = GetStringFromMyResource(MyResourceName_LogErrorInfo);
            if (IsDbLog)
            { codetext = codetext.Replace("//ReplaceHere", GetStringFromMyResource("db_LogAlert")); }
            else
            { codetext = codetext.Replace("//ReplaceHere", GetStringFromMyResource("csv_LogAlert")); }
            codetext = codetext.Replace("@EventType@", "W");
            AddScriptTask(ehOnWarning, OnWarningInfoName,
                "stMSBIHELPERSSISDEMO_LogWarning",
                codetext);
        }
 
        private IDTSPath100 GetPath(MainPipe dataFlowTask, IDTSComponentMetaData100 com,bool isSource)
        {
            foreach (IDTSPath100 path in dataFlowTask.PathCollection)
            {
                //&& path.StartPoint.Component.ComponentClassID.Equals(com.ComponentClassID)
                if (isSource && path.StartPoint.Component.ID.Equals(com.ID))
                {
                    if (path.StartPoint.IsErrorOut)
                        continue;// Just Care the Green output Not Error output ,if error just care the desitination
                    else 
                    return path;
                }
                if (!isSource && path.EndPoint.Component.ID.Equals(com.ID))
                    return path;
            }
           
            return null;
        }

        private void AddPath(MainPipe dataFlowTask, IDTSComponentMetaData100 startpoint,
                             IDTSComponentMetaData100 endpoint, bool isErrorOutput = false)
        {
                if (!isErrorOutput)
                    dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(startpoint.OutputCollection[0],
                                 endpoint.InputCollection[0]);
                else
                {
                    IDTSOutput100 errorOutput = GetErrorOutput(startpoint);
                    dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(errorOutput,
                                 endpoint.InputCollection[0]);
                }  
        }

        private void AddPath(MainPipe dataFlowTask, IDTSOutput100 startpoint, IDTSInput100 endpoint)
        {
            dataFlowTask.PathCollection.New().AttachPathAndPropagateNotifications(startpoint, endpoint);
        }

        private IDTSComponentMetaData100 initialRowCountTask(
            Package p,MainPipe dataFlowTask,
            IDTSComponentMetaData100 com,int counter)
        {
            Variable Counter = p.Variables.Add(
                      string.Format("{0}{1}",RowCountVariableNamePrefix,counter.ToString()), false, "User", 0);
            IDTSComponentMetaData100 rowcountTask = dataFlowTask.ComponentMetaDataCollection.New();
            rowcountTask.ComponentClassID = "DTSTransform.RowCount";
            rowcountTask.Description = string.Format("{0}_{1}",RowCountTaskName,com.Name.Replace(" ", "_"));
            CManagedComponentWrapper wrapper = rowcountTask.Instantiate();
            wrapper.ProvideComponentProperties();// this will change the rowcounttask name to row count
            wrapper.SetComponentProperty("VariableName", Counter.Name);
            rowcountTask.Name = string.Format("{0}_{1}",RowCountTaskName,com.Name.Replace(" ", "_"));
            return rowcountTask;
        }

        private string LinkRowCountPath(
          Package p, MainPipe dataFlowTask, IDTSComponentMetaData100 com,
          IDTSComponentMetaData100 rowcountTask ,bool isSource)
        {
            string outputtype = isSource ? "SRC" : "DEST";
            IDTSPath100 path = GetPath(dataFlowTask, com, isSource);
            //store the old path's startpoint and endpoint
            IDTSOutput100 startpoint = null;
            IDTSInput100 endpoint = null;
            
            if (path != null)
            {
                #region Note someting
                //In fact, I just use endpoint = path.EndPoint and it is ok most of time
                // but I find a error occure when meet a union all component
                //DTS_E_OBJECTNOTINHASHTABLE 
                #endregion
                startpoint = path.StartPoint;
                //if (startpoint.IsErrorOut) outputtype = "BAD";
                if(!isSource)
                    GetOutputType(dataFlowTask, com , ref outputtype);
                IDTSComponentMetaData100 comafter = path.EndPoint.Component;
                dataFlowTask.PathCollection.RemoveObjectByID(path.ID);
                foreach (IDTSInput100 item in comafter.InputCollection)
                {
                    if (!item.IsAttached) { endpoint = item; break; }
                }
                AddPath(dataFlowTask, startpoint, rowcountTask.InputCollection[0]);
                AddPath(dataFlowTask, rowcountTask.OutputCollection[0], endpoint);
             // check if the outputtype is dest and the component'name has bad
                //if (outputtype.Equals("DEST") && endpoint.Component.Name.Contains("BAD"))
                //    sbProcessInfo.AppendLine("******PackageName:" + p.Name + "Component Name:" 
                //        + endpoint.Component.Name + " outputtype:DEST maybe :bad,please check!!");

            }
            return outputtype;
            #region the former
            //bool isErrorOutput = false;
            //if (path != null)
            //{
            //    isErrorOutput = path.StartPoint.IsErrorOut;
            //    if (isSource)
            //    {
            //        IDTSComponentMetaData100 comafter = path.EndPoint.Component;
            //        dataFlowTask.PathCollection.RemoveObjectByID(path.ID);
            //        if (!isErrorOutput)
            //            AddPath(dataFlowTask, com, rowcountTask);
            //        else
            //            AddPath(dataFlowTask, com, rowcountTask, true);
            //        AddPath(dataFlowTask, rowcountTask, comafter);
            //    }
            //    else //destination task
            //    {
            //        IDTSComponentMetaData100 combefore = path.StartPoint.Component;
            //        dataFlowTask.PathCollection.RemoveObjectByID(path.ID);
            //        if (!isErrorOutput)
            //            AddPath(dataFlowTask, combefore, rowcountTask);
            //        else
            //            AddPath(dataFlowTask, combefore, rowcountTask, true);
            //        AddPath(dataFlowTask, rowcountTask, com);
            //    }
            //}
            //else
            //{
            //    if (isSource)
            //        AddPath(dataFlowTask, com, rowcountTask);
            //    else
            //        AddPath(dataFlowTask, rowcountTask, com);
            //}
            #endregion
        }

        private void  GetOutputType(MainPipe dft, IDTSComponentMetaData100 com,ref string outputtype)
        { 
            IDTSPath100 path = GetPath(dft, com, false);
            if (path != null)
            {
                if (path.StartPoint.Component.ObjectType.ToString().Equals("9")
                    || 
                    path.StartPoint.Component.ObjectType.ToString().Equals("10")
                    )
                {
                    if (path.StartPoint.IsErrorOut)
                        outputtype = "BAD";
                    else outputtype = "DEST";
                }
                else
                {
                    com = path.StartPoint.Component;
                    GetOutputType(dft, com,ref outputtype);
                }
            }
        }

        private void AddRowCountTask(Package p,TaskHost th,IDTSComponentMetaData100 com,bool isSource,StringBuilder sb=null)

        {
                string sourcetype = isSource ? "SRC" : "DEST";//BADROWS
                MainPipe dataFlowTask = th.InnerObject as MainPipe;
                if (dataFlowTask == null) return;
                int i = CountVariables(p, RowCountVariableNamePrefix) + 1;
                IDTSComponentMetaData100 rowcountTask = initialRowCountTask(p, dataFlowTask, com, i);
                sourcetype = LinkRowCountPath(p, dataFlowTask, com, rowcountTask, isSource);
                
                if (sb != null)
                {
                    UpdateScriptInfo(sb, p, th, com, isSource, i,sourcetype);
                }
        }
 
        private void UpdateScriptInfo(StringBuilder sb,Package p,TaskHost th, IDTSComponentMetaData100 com,bool isSource,int i,string outputType)
        {
            string codetemplate = GetStringFromMyResource(MyResourceName5);
            if (codetemplate != string.Empty)
            {
                //get sqlquery or filenaem or tablename
                Dictionary<string, string> dict = GetDataObject(p, com, isSource);
                string cmid = string.Empty; string cmname = string.Empty;
                string dataobject = string.Empty; string query = string.Empty;
                string dataObjectType = string.Empty;
                if (dict.ContainsKey("CMID")) cmid = dict["CMID"];
                if (dict.ContainsKey("CMNAME")) cmname = dict["CMNAME"];
                if (dict.ContainsKey("DATAOBJECT")) dataobject = dict["DATAOBJECT"].Replace("\""," ");
                if (dict.ContainsKey("QUERY")) query = dict["QUERY"];
                if (dict.ContainsKey("DATAOBJECTTYPE")) dataObjectType = dict["DATAOBJECTTYPE"];
                string codetext = string.Format(
                    codetemplate,
                    th.ID,
                    cmid,
                    cmname,
                    com.Name,
                    outputType,
                    th.ID + "_" + com.ID.ToString(),
                    dataObjectType,
                    dataobject,
                    query,
                    string.Format("{0}{1}", RowCountVariableNamePrefix, i.ToString())
                    );
                sb.AppendLine(codetext.Replace("^^", "{").Replace("@!@", "}"));
            }
        }
      
        private Dictionary<string,string> GetDataObject(Package p, IDTSComponentMetaData100 com, bool isSource)
        {
            Dictionary<string, string> dict = new Dictionary<string, string>();
            try
            {
                string cmid = com.RuntimeConnectionCollection[0].ConnectionManagerID;
                ConnectionManager cm = p.Connections[cmid];
                if (cm != null)
                {
                    dict.Add("CMID", cm.ID);
                    dict.Add("CMNAME", cm.Name);
                    if (cm.Name.Equals("FLATFILE"))
                        dict.Add("DATAOBJECTTYPE", "FLATFILE");
                    else dict.Add("DATAOBJECTTYPE", cm.CreationName);
                    try
                    {
                        if (cm.CreationName.Equals("OLEDB"))
                        {// use table or view
                            if (isSource && com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("0"))
                                dict.Add("DATAOBJECT", com.CustomPropertyCollection["OpenRowset"].Value.ToString());
                            if (!isSource && (com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("0")
                                || com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("3")
                               ))
                            {
                                dict.Add("DATAOBJECT", com.CustomPropertyCollection["OpenRowset"].Value.ToString().Replace("\""," "));
                                if(dict.ContainsKey("DATAOBJECTTYPE"))
                                dict["DATAOBJECTTYPE"] = "TableOrView";
                            }
                        }   
                    }
                    catch (Exception EX) { sbProcessInfo.AppendLine(EX.Message + Environment.NewLine + EX.StackTrace); }
                    try
                    {
                        if (cm.CreationName.Equals("OLEDB")
                            &&
                            com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("2")
                            )// 2 use the sql query so we can get the sqlcommand property value
                        {
                            //Replace the Query's special char such as "
                            dict.Add("QUERY", com.CustomPropertyCollection["SqlCommand"].Value.ToString().Replace("\""," "));
                            if (dict.ContainsKey("DATAOBJECTTYPE"))
                                dict["DATAOBJECTTYPE"] = "Query";
                        }
                    }
                    catch (Exception EX) { sbProcessInfo.AppendLine(EX.Message + Environment.NewLine + EX.StackTrace); }
                }
            }
            catch{}
            return dict;
        }

        //log this in the add row count task before just one time one package 
        public void LogPreExecute(Package p,bool isAddAudit)
        {
            //if isAddAudit remove PreExecute task
            //remove script task then remove the variable
            DtsEventHandler ehOnPreExecute = getEventHandler(p, sEventType.OnPreExecute);
            //Remove Event Handler script task
            if (!isAddAudit)
            {
                RemoveScriptTask(p, ehOnPreExecute, OnPreExecuteEventHandlerTaskName);
                return;
            }
            //MSBIHelper_Log_Conn
            if (IsDbLog)
            {
                string connstr = ConfigurationManager.AppSettings["MSBIHELPER_LOG_CONN"].ToString();
                if (!p.Variables.Contains("MSBIHelper_Log_Conn")) p.Variables.Add("MSBIHelper_Log_Conn", false, "User", connstr);
            }
            else
            {
                if (!p.Variables.Contains("MSBIHelper_CSVFilePath")) p.Variables.Add("MSBIHelper_CSVFilePath", false, "User", csvfilepath);
                if (!p.Variables.Contains("MSBIHelper_GenarateDailyFolder")) p.Variables.Add("MSBIHelper_GenarateDailyFolder", false, "User", genarateDailyFolder);
            }

            //GetScript
            string script = GetStringFromMyResource(MyResourceName4);
            if (IsDbLog)
                script = script.Replace("//ReplaceHere", GetStringFromMyResource("db_PreExecute"));
            else
                script = script.Replace("//ReplaceHere", GetStringFromMyResource("csv_PreExecute"));
            AddScriptTask(ehOnPreExecute, OnPreExecuteEventHandlerTaskName, "stMSBIHELPERSSISDEMO_PreExecute",script);
        }
         
        private IDTSOutput100 GetErrorOutput(IDTSComponentMetaData100 com)
        {
            foreach (IDTSOutput100 item in com.OutputCollection)
            {
                if (item.IsErrorOut) return item;
            }
            return null;
        }

        private  void ProcessPipeline(Package p,TaskHost th,bool isAddAudit=true,StringBuilder sbscripts=null)
        {
            MainPipe pipeline = th.InnerObject as MainPipe;
            if (pipeline == null) return;
            List<IDTSComponentMetaData100> comlist = new List<IDTSComponentMetaData100>();
            foreach (IDTSComponentMetaData100 componentMetadata in pipeline.ComponentMetaDataCollection)
            {
                if (
                    componentMetadata.ObjectType.ToString().Equals("9")//source component
                    ||
                    componentMetadata.ObjectType.ToString().Equals("10")//destination component
                   )
                    comlist.Add(componentMetadata);
                
            }
            // control one package 's audit frame work
            if (comlist.Count < 1) return;
            foreach (IDTSComponentMetaData100 com in comlist)
            {
                if (com.ObjectType.ToString().Equals("9"))//source task
                {
                    //GetDataObjectAndQuery(p, pipeline, com);
                    if (isAddAudit)
                        AddRowCountTask(p, th, com, true,sbscripts);
                    else
                        RemoveRowCountTask(p, pipeline, com, true);
                }
                if (com.ObjectType.ToString().Equals("10"))//destination task add audit
                {
                    if (isAddAudit)
                        AddRowCountTask(p, th, com, false,sbscripts);
                    else
                        RemoveRowCountTask(p, pipeline, com, false);
                }
            }
        }
 
        public   void AuditFramework(Package p, Executables executables,bool isAddAudit=true)
        {
            //add Pre Execute Log 
            //try { LogPreExecute(p,isAddAudit); }catch { }
            //StringBuilder sbscripts = new StringBuilder();
            //if container is disabled then do nothing
            foreach (Executable executable in executables)
            {
                TaskHost taskHost = executable as TaskHost;
                if (taskHost != null && !taskHost.Disable)
                {
                    if (taskHost.InnerObject is MainPipe)
                        ProcessPipeline(p, taskHost, isAddAudit,sbscripts);
                    if (taskHost.InnerObject is ExecuteSQLTask)
                        ProcessExecuteSqlTask(p,taskHost,isAddAudit);
                    continue;
                }

                Sequence sequence = executable as Sequence;
                if (sequence != null && !sequence.Disable)
                {
                    AuditFramework(p, sequence.Executables,isAddAudit);
                    continue;
                }

                ForLoop forLoop = executable as ForLoop;
                if (forLoop != null && !forLoop.Disable)
                {
                    AuditFramework(p,forLoop.Executables,isAddAudit);
                    continue;
                }

                ForEachLoop forEachLoop = executable as ForEachLoop;
                if (forEachLoop != null && !forEachLoop.Disable)
                {
                    AuditFramework(p,forEachLoop.Executables,isAddAudit);
                    continue;
                }
            }
        }
 
        private  void RemoveRowCountTask(Package p, MainPipe dataFlowTask, IDTSComponentMetaData100 com, bool isSource)
        {
            //find the MSBIHelper_RowCount_ rowcountTask then remove
            //for one rowcount Task I should remove two path and then add one new path
            // find the two path's one's start point and the other path's end point component
            IDTSPath100 path1 = GetPath(dataFlowTask, com, isSource);
            //path1.EndPoint.Component should be the RowcountTask
            if (path1 == null) return;
            IDTSOutput100 startpoint = null;
            IDTSInput100 endpoint = null;
            IDTSComponentMetaData100 rowCountTask = null;
            IDTSComponentMetaData100 comAfter = null;
            if (isSource)
                rowCountTask = path1.EndPoint.Component;
            else
                rowCountTask = path1.StartPoint.Component;
            if(rowCountTask==null ||(rowCountTask!=null && !rowCountTask.Name.Contains(RowCountTaskName)))return;
                IDTSPath100 path2 = GetPath(dataFlowTask, rowCountTask, isSource);
                if (path2!=null)
                {
                    if (isSource)
                    {
                        startpoint = path1.StartPoint;
                        comAfter = path2.EndPoint.Component;
                    }
                    else
                    {
                        startpoint = path2.StartPoint;
                        comAfter = path1.EndPoint.Component;
                    }
                    dataFlowTask.PathCollection.RemoveObjectByID(path1.ID);
                    dataFlowTask.PathCollection.RemoveObjectByID(path2.ID);
                    foreach (IDTSInput100 item in comAfter.InputCollection)
                    {
                        if (!item.IsAttached) { endpoint = item; break; }
                    }
                    AddPath(dataFlowTask, startpoint, endpoint);

                    #region the former method
                    //IDTSComponentMetaData100 com2 = null;
                    //if(isSource)
                    //    com2 = path2.EndPoint.Component;
                    //else
                    //   com2 = path2.StartPoint.Component;
                    //// judge the path1.iserrorout property
                    //bool isErrorOut = (isSource ? path1.StartPoint.IsErrorOut : path2.StartPoint.IsErrorOut);
                    ////delete path1 path2 
                    //dataFlowTask.PathCollection.RemoveObjectByID(path1.ID);
                    //dataFlowTask.PathCollection.RemoveObjectByID(path2.ID);
                    //if (isSource)
                    //    AddPath(dataFlowTask, com, com2, isErrorOut);
                    //else
                    //    AddPath(dataFlowTask, com2, com, isErrorOut);
                    #endregion
                }
                //remove 
                dataFlowTask.ComponentMetaDataCollection.RemoveObjectByID(rowCountTask.ID);
        }

        private  void RemoveVariables(Package p, string pattern)
        {
            List<string> variableList = new List<string>();
            foreach (var var in p.Variables)
            {
                if (var.Name.StartsWith(pattern))
                    variableList.Add(var.Name);
            }
            foreach (var item in variableList)
            {
                p.Variables.Remove(item);
            }
        }

        public string GetDataObjectAndQuery(Package p,MainPipe pipeline,IDTSComponentMetaData100 com)
        {
            //design through the com find the related connection manager and get the related info
            string rst = string.Empty;
            string cmid = com.RuntimeConnectionCollection[0].ConnectionManagerID;
            rst += "connection manager id :" + cmid + Environment.NewLine;
            ConnectionManager cm = p.Connections[cmid];
            if(cm!=null)
            {
                rst += "connection manager name:" + cm.Name + Environment.NewLine;
                rst += "connection manager id:" + cm.ID.ToString() + Environment.NewLine;
                rst += "com name:" + com.Name + Environment.NewLine;
               
                try
                {
                    if (cm.CreationName.Equals("OLEDB")
                        &&
                        (
                        com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("0")
                        ||
                        com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("3")
                        )
                        )// use table or view
                            rst += "table or view name:" +com.CustomPropertyCollection["OpenRowset"].Value.ToString() + Environment.NewLine;
                }catch { }
                try
                {
                    if (cm.CreationName.Equals("OLEDB")
                        &&
                        com.CustomPropertyCollection["AccessMode"].Value.ToString().Equals("2")
                        )// 2 use the sql query so we can get the sqlcommand property value
                        rst += "table query " + 
                            com.CustomPropertyCollection["SqlCommand"].Value.ToString().Replace("\""," ")
                            + Environment.NewLine;
                }
                catch { }
            }

            return rst;
        }

        public void AddAuditFramework(string pkgpath,bool isAddAudit)
        { 
            //add OnPreExecute Event handler
            //add Rowcount tasks
            //add OnError event handler
            //add OnPostExecute event handler
            try
            {
                sbProcessInfo.Remove(0, sbProcessInfo.Length);
                Package p = sqlPackageLoader.LoadPackage(pkgpath, null);
                if (isAddAudit && hasAddedAuditFramework(ref p))
                { sbProcessInfo.AppendLine("package :" + p.Name + " Has add audit framework ,return"); return; }
                if (!isAddAudit && !hasAddedAuditFramework(ref p))
                { sbProcessInfo.AppendLine("package :" + p.Name + " Has not added audit framework ,return"); return; }
                
                LogPreExecute(p, isAddAudit);
                sbscripts.Remove(0, sbscripts.Length);
                sbSTMT.Remove(0, sbSTMT.Length);
                AuditFramework(p, p.Executables, isAddAudit);
                LogPostExecute(p, isAddAudit);
                LogPkgErrorInfo(p, isAddAudit);
                LogPkgWarningInfo(p, isAddAudit);
                if (!isAddAudit)
                {
                    //RemoveVariables(p, RowCountVariableNamePrefix);
                    RemoveVariables(p, "MSBIHelper_");
                }
                //if there are exceptions in the process then not save file
                if(sbProcessInfo.ToString().IndexOf("ErrorOccure")==-1)
                sqlPackageLoader.SaveToXml(pkgpath, p, null);

                if (isAddAudit)
                    sbProcessInfo.AppendLine("Has finished add AuditFramework for package :" + p.Name);
                else
                    sbProcessInfo.AppendLine("Has finished remove AuditFramework for package :" + p.Name);
            }
            catch (Exception EX)
            {
                sbProcessInfo.AppendLine("ErrorOccure");
                sbProcessInfo.AppendLine("Error when process package:" + pkgpath+Environment.NewLine);
                sbProcessInfo.AppendLine(EX.Message + Environment.NewLine + EX.StackTrace);
            }
        }
         
        public bool hasAddedAuditFramework(ref Package p)
        { 
            int flagcount = CountVariables(p, "MSBIHelper_");
            return flagcount>0;
        }
        // add one variable for ExecuteSqlTask To Capture affect Row count
        private void ProcessExecuteSqlTask(Package p, TaskHost EST, bool isAudit)
        {
            try
            {
                // if the task is disable then return 
                if (EST.Disable) return;
                ExecuteSQLTask myest = EST.InnerObject as ExecuteSQLTask;
                //if task name contains Truncate then return not care
                if (EST.Name.ToLower().Contains("truncate")) return;
                if (myest == null) return;
                if (isAudit)
                {
                    //Specify the ExecValueVariable value
                    // code to write      
                    string codetemplate = GetStringFromMyResource(MyResourceName5);
                    if (codetemplate != string.Empty)
                    {
                        int i = CountVariables(p, ESTVariableNamePrefix) + 1;
                        //Add Variable
                        string vname = string.Format("{0}{1}", ESTVariableNamePrefix, i.ToString());
                        Variable v = p.Variables.Add(vname, false, "User", 0);
                        EST.ExecValueVariable = v;
                        string codetext = string.Format(
                            codetemplate,
                            EST.ID,
                            "Unknown",
                            "Unknown",
                            EST.Name,
                            "DEST",
                            EST.ID,
                            myest.SqlStatementSourceType.ToString().Replace("\""," "),
                            myest.Connection.Replace("\""," "),
                            myest.SqlStatementSource.Replace("\""," "),
                            vname);
                        sbSTMT.AppendLine(codetext.Replace("^^", "{").Replace("@!@", "}"));
                    }
                }
                else
                {
                    //Remove Variable 
                    //Remove ExecValueVariable attribute value
                    if (EST.ExecValueVariable != null)
                    {
                        Variable v = EST.ExecValueVariable;
                        EST.ExecValueVariable = null;
                        p.Variables.Remove(v);
                    }
                }
            }
            catch (Exception EX)
            {
                sbProcessInfo.AppendLine("Error when process package:" + p.GetPackagePath() + Environment.NewLine);
                sbProcessInfo.AppendLine(EX.Message + Environment.NewLine + EX.StackTrace);
            }
        }
         
        #endregion

        #region ParallelAuditFramework
        private void RemoveScriptTask2(Package p, DtsEventHandler eh)
        {
            //remove scripttask like MSBIHelper_..... in this event handler
            string tasknamepattern = "MSBIHelper_";
            List<string> scripttasklist = new List<string>();
            foreach (Executable item in eh.Executables)
            {
                if ((item as TaskHost).Name.Contains(tasknamepattern))
                    scripttasklist.Add((item as TaskHost).Name);
            }
            foreach (string stname in scripttasklist)
            {
                eh.Executables.Remove(stname);
            }
            if (eh.Executables.Count.Equals(0))
                p.EventHandlers.Remove(eh.Name);
        }
        private void AddScriptTask2(DtsEventHandler eh, string taskname, string projectName, string codeText, ParaObject po)
        {
            Executable e = eh.Executables.Add("STOCK:ScriptTask");
            TaskHost myst = e as TaskHost;
            myst.Name = taskname;
            ScriptTask st = myst.InnerObject as ScriptTask;
            try
            {
                string guid = Guid.NewGuid().ToString().Replace("-","");
                st.ScriptProjectName ="pj_"+ guid;
                st.ScriptLanguage = scriptlanguage;

                st.ScriptingEngine.InitNewScript("CSharp", st.ScriptProjectName, ".csproj");
                st.ScriptingEngine.ShowDesigner(false);
                st.ScriptingEngine.AddCodeFile("ScriptMain.cs", codeText.Replace("*ProjectName*", guid));
                if (!st.ScriptingEngine.Build())
                {
                    po.sbProcessInfo.AppendLine("ErrorOccure");
                    po.sbProcessInfo.AppendLine("build script task fail, package :" + eh.Parent.Name + " event handler:" + eh.Name);
                    return;
                }
                st.ScriptingEngine.SaveScriptToStorage();
            }
            catch (Exception eX)
            {
                po.sbProcessInfo.AppendLine("ErrorOccure");
                po.sbProcessInfo.AppendLine("Exception in package :" + eh.Parent.Name);
                po.sbProcessInfo.AppendLine(eX.Message + Environment.NewLine + eX.StackTrace);
                System.Diagnostics.Debug.WriteLine(
                    eX.Message + Environment.NewLine + eX.StackTrace
                    );
            }
            finally
            {
                st.ScriptingEngine.CloseIDE(true);
            }
        }
        private void LogPostExecute2(Package p, ParaObject po)
        {
            DtsEventHandler ehOnPostExecute = getEventHandler(p, sEventType.OnPostExecute);
            if (!po.isAudit)
            {
                RemoveScriptTask2(p, ehOnPostExecute);
                RemoveVariables(p, sMSBIHelper_Conn);
                return;
            }
            if (IsDbLog)
            {
                string connstr = ConfigurationManager.AppSettings["MSBIHELPER_LOG_CONN"].ToString();
                if (!p.Variables.Contains("MSBIHelper_Log_Conn")) p.Variables.Add("MSBIHelper_Log_Conn", false, "User", connstr);
            }
            else
            {
                if (!p.Variables.Contains("MSBIHelper_CSVFilePath")) p.Variables.Add("MSBIHelper_CSVFilePath", false, "User", csvfilepath);
                if (!p.Variables.Contains("MSBIHelper_GenarateDailyFolder")) p.Variables.Add("MSBIHelper_GenarateDailyFolder", false, "User", genarateDailyFolder);
            }

            po.sbScripts.AppendLine(po.sbSTMT.ToString());
            string codetext = GetStringFromMyResource(MyResourceName6).Replace("/*REPLACE YOUR CODE HERE PLEASE*/", po.sbScripts.ToString());
            if (IsDbLog)
            {
                codetext = codetext.Replace("//ReplaceHereUpdatePkgInfo", GetStringFromMyResource("db_PE_UpdatePkgInfo"));
                codetext = codetext.Replace("//ReplaceHereRowcountinfo", GetStringFromMyResource("db_PE_UpdateRowInfo"));
            }
            else
            {
                codetext = codetext.Replace("//ReplaceHereUpdatePkgInfo", GetStringFromMyResource("csv_PE_UpdatePkgInfo"));
                codetext = codetext.Replace("//ReplaceHereRowcountinfo", GetStringFromMyResource("csv_PE_UpdateRowInfo"));
            }
            AddScriptTask2(ehOnPostExecute, OnPostExecuteEventHandlerScriptTaskName, "stMSBIHELPERSSISDEMO_UpdatePkgInfo", codetext, po);
        }
        private void LogPkgErrorInfo2(Package p, ParaObject po)
        {
            //add one script task on error event handler
            DtsEventHandler ehOnError = getEventHandler(p, sEventType.OnError);
            //Remove 
            if (!po.isAudit && ehOnError != null)
            {
                RemoveScriptTask(p, ehOnError, OnErrorInsertErrorInfoName);
                return;
            }
            string codetext = GetStringFromMyResource(MyResourceName_LogErrorInfo);
            if (IsDbLog)
            { codetext = codetext.Replace("//ReplaceHere", GetStringFromMyResource("db_LogAlert")); }
            else
            { codetext = codetext.Replace("//ReplaceHere", GetStringFromMyResource("csv_LogAlert")); }
            codetext = codetext.Replace("@EventType@", "E");
            AddScriptTask2(ehOnError, OnErrorInsertErrorInfoName, "stMSBIHELPERSSISDEMO_LogError", codetext, po);
        }
        private void LogPkgWarningInfo2(Package p, ParaObject po)
        {
            //add one script task on warning event handler
            DtsEventHandler ehOnWarning = getEventHandler(p, sEventType.OnWarning);
            //Remove 
            if (!po.isAudit && ehOnWarning != null)
            {
                RemoveScriptTask(p, ehOnWarning, OnWarningInfoName);
                return;
            }
            string codetext = GetStringFromMyResource(MyResourceName_LogErrorInfo);
            if (IsDbLog)
            { codetext = codetext.Replace("//ReplaceHere", GetStringFromMyResource("db_LogAlert")); }
            else
            { codetext = codetext.Replace("//ReplaceHere", GetStringFromMyResource("csv_LogAlert")); }
            codetext = codetext.Replace("@EventType@", "W");
            AddScriptTask2(ehOnWarning, OnWarningInfoName,
                "stMSBIHELPERSSISDEMO_LogWarning",
                codetext, po);
        }
        private void LogPreExecute2(Package p, ParaObject po)
        {
            DtsEventHandler ehOnPreExecute = getEventHandler(p, sEventType.OnPreExecute);
            //Remove Event Handler script task
            if (!po.isAudit)
            {
                RemoveScriptTask(p, ehOnPreExecute, OnPreExecuteEventHandlerTaskName);
                return;
            }
            //MSBIHelper_Log_Conn
            if (IsDbLog)
            {
                string connstr = ConfigurationManager.AppSettings["MSBIHELPER_LOG_CONN"].ToString();
                if (!p.Variables.Contains("MSBIHelper_Log_Conn")) p.Variables.Add("MSBIHelper_Log_Conn", false, "User", connstr);
            }
            else
            {
                if (!p.Variables.Contains("MSBIHelper_CSVFilePath")) p.Variables.Add("MSBIHelper_CSVFilePath", false, "User", csvfilepath);
                if (!p.Variables.Contains("MSBIHelper_GenarateDailyFolder")) p.Variables.Add("MSBIHelper_GenarateDailyFolder", false, "User", genarateDailyFolder);
            }

            string script = GetStringFromMyResource(MyResourceName4);
            if (IsDbLog)
                script = script.Replace("//ReplaceHere", GetStringFromMyResource("db_PreExecute"));
            else
                script = script.Replace("//ReplaceHere", GetStringFromMyResource("csv_PreExecute"));
            AddScriptTask2(ehOnPreExecute, OnPreExecuteEventHandlerTaskName, "stMSBIHELPERSSISDEMO_PreExecute", script, po);
        }
        private void ProcessPipeline2(Package p, TaskHost th, ParaObject po)
        {
            MainPipe pipeline = th.InnerObject as MainPipe;
            if (pipeline == null) return;
            List<IDTSComponentMetaData100> comlist = new List<IDTSComponentMetaData100>();
            foreach (IDTSComponentMetaData100 componentMetadata in pipeline.ComponentMetaDataCollection)
            {
                if (
                    componentMetadata.ObjectType.ToString().Equals("9")//source component
                    ||
                    componentMetadata.ObjectType.ToString().Equals("10")//destination component
                   )
                    comlist.Add(componentMetadata);

            }
            // control one package 's audit frame work
            if (comlist.Count < 1) return;
            foreach (IDTSComponentMetaData100 com in comlist)
            {
                if (com.ObjectType.ToString().Equals("9"))//source task
                {
                    //GetDataObjectAndQuery(p, pipeline, com);
                    if (po.isAudit)
                        AddRowCountTask(p, th, com, true, po.sbScripts);
                    else
                        RemoveRowCountTask(p, pipeline, com, true);
                }
                if (com.ObjectType.ToString().Equals("10"))//destination task add audit
                {
                    if (po.isAudit)
                        AddRowCountTask(p, th, com, false, po.sbScripts);
                    else
                        RemoveRowCountTask(p, pipeline, com, false);
                }
            }
        }
        private void AddRowCountTask2(Package p, TaskHost th, IDTSComponentMetaData100 com, ParaObject po)
        {
            string sourcetype = po.isAudit ? "SRC" : "DEST";//BADROWS
            MainPipe dataFlowTask = th.InnerObject as MainPipe;
            if (dataFlowTask == null) return;
            int i = CountVariables(p, RowCountVariableNamePrefix) + 1;
            IDTSComponentMetaData100 rowcountTask = initialRowCountTask(p, dataFlowTask, com, i);
            sourcetype = LinkRowCountPath(p, dataFlowTask, com, rowcountTask, po.isAudit);

            UpdateScriptInfo(po.sbScripts, p, th, com, po.isAudit, i, sourcetype);

        }
        private void AuditFramework2(Package p, Executables executables, ParaObject po)
        {
            foreach (Executable executable in executables)
            {
                TaskHost taskHost = executable as TaskHost;
                if (taskHost != null && !taskHost.Disable)
                {
                    if (taskHost.InnerObject is MainPipe)
                        ProcessPipeline2(p, taskHost, po);
                    if (taskHost.InnerObject is ExecuteSQLTask)
                        ProcessExecuteSqlTask2(p, taskHost, po);
                    continue;
                }

                Sequence sequence = executable as Sequence;
                if (sequence != null && !sequence.Disable)
                {
                    AuditFramework2(p, sequence.Executables, po);
                    continue;
                }

                ForLoop forLoop = executable as ForLoop;
                if (forLoop != null && !forLoop.Disable)
                {
                    AuditFramework2(p, forLoop.Executables, po);
                    continue;
                }

                ForEachLoop forEachLoop = executable as ForEachLoop;
                if (forEachLoop != null && !forEachLoop.Disable)
                {
                    AuditFramework2(p, forEachLoop.Executables, po);
                    continue;
                }
            }
        }
        private void ProcessExecuteSqlTask2(Package p, TaskHost EST, ParaObject po)
        {
            try
            {
                // if the task is disable then return 
                if (EST.Disable) return;
                ExecuteSQLTask myest = EST.InnerObject as ExecuteSQLTask;
                //if task name contains Truncate then return not care
                if (EST.Name.ToLower().Contains("truncate")) return;
                if (myest == null) return;
                if (po.isAudit)
                {
                    //Specify the ExecValueVariable value
                    // code to write      
                    string codetemplate = GetStringFromMyResource(MyResourceName5);
                    if (codetemplate != string.Empty)
                    {
                        int i = CountVariables(p, ESTVariableNamePrefix) + 1;
                        //Add Variable
                        string vname = string.Format("{0}{1}", ESTVariableNamePrefix, i.ToString());
                        Variable v = p.Variables.Add(vname, false, "User", 0);
                        EST.ExecValueVariable = v;
                        string codetext = string.Format(
                            codetemplate,
                            EST.ID,
                            "Unknown",
                            "Unknown",
                            EST.Name,
                            "DEST",
                            EST.ID,
                            myest.SqlStatementSourceType.ToString().Replace("\"", " "),
                            myest.Connection.Replace("\"", " "),
                            myest.SqlStatementSource.Replace("\"", " "),
                            vname);
                        po.sbSTMT.AppendLine(codetext.Replace("^^", "{").Replace("@!@", "}"));
                    }
                }
                else
                {
                    if (EST.ExecValueVariable != null)
                    {
                        Variable v = EST.ExecValueVariable;
                        EST.ExecValueVariable = null;
                        p.Variables.Remove(v);
                    }
                }
            }
            catch (Exception EX)
            {
                po.sbProcessInfo.AppendLine("Error when process package:" + p.GetPackagePath() + Environment.NewLine);
                po.sbProcessInfo.AppendLine(EX.Message + Environment.NewLine + EX.StackTrace);
            }
        }
        public  void AddAuditFramework2(object obj)
        {
            ParaObject po = obj as ParaObject;
            if (po == null) return; 
            try
            { 
                Package p = sqlPackageLoader.LoadPackage(po.pkgpath, null);
                if (po.isAudit && hasAddedAuditFramework(ref p))
                { po.sbProcessInfo.AppendLine("package :" + p.Name + " Has add audit framework ,return"); return; }
                if (!po.isAudit && !hasAddedAuditFramework(ref p))
                { po.sbProcessInfo.AppendLine("package :" + p.Name + " Has not added audit framework ,return"); return; }
                LogPreExecute2(p, po);
                AuditFramework2(p, p.Executables, po);
                LogPostExecute2(p, po);
                LogPkgErrorInfo2(p, po);
                LogPkgWarningInfo2(p, po);
                if (!po.isAudit)
                {
                    RemoveVariables(p, "MSBIHelper_");
                }
                if (po.sbProcessInfo.ToString().IndexOf("ErrorOccure") == -1)
                    sqlPackageLoader.SaveToXml(po.pkgpath, p, null);
                if (po.isAudit)
                    po.sbProcessInfo.AppendLine("Has finished add AuditFramework for package :" + p.Name);
                else
                    po.sbProcessInfo.AppendLine("Has finished remove AuditFramework for package :" + p.Name);
                System.Diagnostics.Debug.WriteLine(po.pkgpath + "finished");
            }
            catch (Exception EX)
            {
                po.sbProcessInfo.AppendLine("Error when process package:" + po.pkgpath + Environment.NewLine);
                po.sbProcessInfo.AppendLine(EX.Message + Environment.NewLine + EX.StackTrace);
                System.Diagnostics.Debug.WriteLine(po.pkgpath + "error occur");
            }
        }
        public void UpdateVariables(string pkgpath,Dictionary<string, string> dict)
        {
            Package p = sqlPackageLoader.LoadPackage(pkgpath, null);
            foreach  (KeyValuePair<string,string> kv in dict)
            {
                if(p.Variables.Contains(kv.Key))
                    p.Variables[kv.Key].Value = kv.Value;
            }
            sqlPackageLoader.SaveToXml(pkgpath, p, null);
        }

        #endregion

        #region AddConfiguration
        public void AddConfiguration(string pkgpath)
        {
            try
            {
                Package p = sqlPackageLoader.LoadPackage(pkgpath, null);
                //Remove all connection before:
                foreach (var item in p.Configurations)
                {
                    p.Configurations.Remove(item);
                }
                int i_uerviriablecount =0;
                p.EnableConfigurations = true;
                Microsoft.SqlServer.Dts.Runtime.
                  Configuration config = p.Configurations.Add();
                config.Name = "Config_" + p.Name;
                config.ConfigurationType = DTSConfigurationType.ConfigFile;
                config.ConfigurationString = string.Format("{1}Config\\Config_{0}.dtsConfig",
                            p.Name, Path.GetDirectoryName(pkgpath).ToUpper().Replace("DTSX", ""));
                string configFolder = Path.GetDirectoryName(pkgpath).ToUpper().Replace("DTSX", "Config");
                foreach (Variable v in p.Variables)
                {
                    if (v.Namespace != "System"
                        && v.DataType != TypeCode.Object //Add 2013/09/13
                        )
                    {
                        v.Properties["Value"].SetExport(v, true); 
                        i_uerviriablecount++; 
                    }
                }
                if (i_uerviriablecount > 0)
                {
                    if (!Directory.Exists(configFolder))
                        Directory.CreateDirectory(configFolder);
                    p.ExportConfigurationFile(config.ConfigurationString);  
                }
                else
                    sbProcessInfo.AppendLine(pkgpath + " not has any user variables");
                //Add another Configuration File For DB Connection
                foreach (var con in p.Connections)
                {
                    //find the related db configuration file in config folder
                    if(con.CreationName=="OLEDB")
                    {
                        //DS_SHARED IC_CLQR.DB. RZ_CLQR.DB.DTSCONFIG
                        string configurationString = configFolder + "\\" + con.Name + ".DB.dtsConfig";
                        //if (File.Exists(configurationString))
                        //{}
                            config = p.Configurations.Add();
                            config.Name = con.Name.Replace(" ","_");
                            config.ConfigurationType = DTSConfigurationType.ConfigFile;
                            config.ConfigurationString = configurationString;
                            p.ExportConfigurationFile(config.ConfigurationString);  
                    }
                }
                sqlPackageLoader.SaveToXml(pkgpath, p, null);
               
            }
            catch (Exception eX)
            {
                sbProcessInfo.AppendLine("ErrorOccure");
                sbProcessInfo.AppendLine("Exception in package :" + pkgpath);
                sbProcessInfo.AppendLine(eX.Message);
                sbProcessInfo.AppendLine(eX.StackTrace);
            }
           

        }

     
        public void GenerateBatch(string pkgpath)
        {
            try
            {
                 sbProcessInfo.Remove(0, sbProcessInfo.Length);
                 Package p =  sqlPackageLoader.LoadPackage(pkgpath, null);
                string batcontent = string.Format("DTEXEC /FILE \"{0}\"",pkgpath);
                if (p.EnableConfigurations)
                {
                    foreach (var cf in p.Configurations)
                    {
                        if (cf.ConfigurationType.Equals(DTSConfigurationType.ConfigFile))
                            batcontent += string.Format(" /CONFIGFILE \"{0}\"", cf.ConfigurationString);
                    }
                }
                batcontent += " /CHECKPOINTING OFF /REPORTING EWCDI";
                string batchfolder = Path.GetDirectoryName(pkgpath).ToUpper().Replace("DTSX", "Bat\\");
                if (!Directory.Exists(batchfolder))
                    Directory.CreateDirectory(batchfolder);
                File.AppendAllText(Path.GetDirectoryName(pkgpath).ToUpper().Replace("DTSX","Bat\\")
                    +"BAT_"
                    +Path.GetFileNameWithoutExtension(pkgpath)//p.Name
                    +".bat",batcontent);
            }
            catch (Exception eX)
            {
                 sbProcessInfo.AppendLine("ErrorOccure");
                sbProcessInfo.AppendLine("Exception in package :" + pkgpath);
                sbProcessInfo.AppendLine(eX.Message);
                sbProcessInfo.AppendLine(eX.StackTrace);
            }
        }
        #endregion

        #region Test
        public string explorerConnections(Package p)
        {
            StringBuilder sb = new StringBuilder();
             
            foreach (ConnectionManager cm in p.Connections)
            {
                sb.Append(string.Format("connectstring:{0}{1}", cm.ConnectionString, Environment.NewLine));
                sb.Append(string.Format("CreationName:{0}{1}", cm.CreationName, Environment.NewLine));
                sb.Append(string.Format("Name:{0}{1}", cm.Name, Environment.NewLine));
                foreach (DtsProperty item in cm.Properties)
                {
                    try
                    { 
                        sb.Append(string.Format("----PropertyName:{0} value:{1}{2}", item.Name,
                        item.GetValue(cm),
                        Environment.NewLine
                        ));
                    }
                    catch { }

                }
            }
            return sb.ToString();
        }
        public string explorerPackageInfo(Executables executables,StringBuilder sb)
        {
            foreach (Executable executable in executables)
            {
                TaskHost taskHost = executable as TaskHost;
                if (taskHost != null)
                {
                    LogTaskinfo(taskHost, sb);
                    continue;
                }

                Sequence sequence = executable as Sequence;
                if (sequence != null)
                {
                    explorerPackageInfo(sequence.Executables, sb);
                    continue;
                }

                ForLoop forLoop = executable as ForLoop;
                if (forLoop != null)
                {
                    explorerPackageInfo(forLoop.Executables, sb);
                    continue;
                }

                ForEachLoop forEachLoop = executable as ForEachLoop;
                if (forEachLoop != null)
                {
                    explorerPackageInfo(forEachLoop.Executables, sb);
                    continue;
                }
            }
            return sb.ToString();
        }
        public void LogTaskinfo(TaskHost th,StringBuilder sb)
        {
            sb.AppendFormat("TaskID:{0}{1}", th.ID, Environment.NewLine);
            sb.AppendFormat("Name:{0}{1}", th.Name, Environment.NewLine);
            sb.AppendFormat("Task CreationName:{0}{1}", th.CreationName, Environment.NewLine);
            foreach (DtsProperty item in th.Properties)
            {
                try
                {
                    sb.AppendFormat("----PropertyName:{0} value:{1}{2}",
                    item.Name,
                    item.GetValue(th),
                    Environment.NewLine);
                }
                catch { continue; }

            }
            //dispose dataflow inner component
            if (th.InnerObject is MainPipe)
            { 
                MainPipe dataflowtask = th.InnerObject as MainPipe;
                sb.Append("taskid of the task:" +th.ID); 
                foreach (IDTSComponentMetaData100 com in dataflowtask.ComponentMetaDataCollection)
                {
                    sb.AppendFormat("------ComponentName:{0} {1}",
                   com.Name,
                   Environment.NewLine);
                }
            }
        }
        public void AddTrimDerivedColumns(string filepath)
        {
            Package p = sqlPackageLoader.LoadPackage(filepath, null);
            Explorercomponents(p, p.Executables, true);
            sqlPackageLoader.SaveToXml(filepath, p, null);
        }
        private void GetDataSource(Package p, TaskHost th, bool isAddAudit = true)
        {
            MainPipe pipeline = th.InnerObject as MainPipe;
            if (pipeline == null) return;
            List<IDTSComponentMetaData100> comlist = new List<IDTSComponentMetaData100>();
            foreach (IDTSComponentMetaData100 componentMetadata in pipeline.ComponentMetaDataCollection)
            {
                if (
                    componentMetadata.ObjectType.ToString().Equals("9")
                   )
                    comlist.Add(componentMetadata);
            }
            if (comlist.Count < 1) return;
            foreach (IDTSComponentMetaData100 com in comlist)
            {
                AddTrim(com, pipeline);
            }
        }
        public void  Explorercomponents(Package p, Executables executables, bool isAddAudit = true)
        {
            foreach (Executable executable in executables)
            {
                TaskHost taskHost = executable as TaskHost;
                if (taskHost != null && !taskHost.Disable)
                {
                    if (taskHost.InnerObject is MainPipe)
                        GetDataSource(p, taskHost, true);
                    continue;
                }

                Sequence sequence = executable as Sequence;
                if (sequence != null && !sequence.Disable)
                {
                    Explorercomponents(p, sequence.Executables, isAddAudit);
                    continue;
                }

                ForLoop forLoop = executable as ForLoop;
                if (forLoop != null && !forLoop.Disable)
                {
                    Explorercomponents(p, forLoop.Executables, isAddAudit);
                    continue;
                }

                ForEachLoop forEachLoop = executable as ForEachLoop;
                if (forEachLoop != null && !forEachLoop.Disable)
                {
                    Explorercomponents(p, forEachLoop.Executables, isAddAudit);
                    continue;
                }
            }
        }
        private void AddTrim(IDTSComponentMetaData100 ds, MainPipe dft)
        {
             IDTSComponentMetaData100 TrimDervCol = dft.ComponentMetaDataCollection.New();
             TrimDervCol.ComponentClassID = "DTSTransform.DerivedColumn";
             CManagedComponentWrapper DesignDerivedTransformColumns = TrimDervCol.Instantiate();
             DesignDerivedTransformColumns.ProvideComponentProperties();
             TrimDervCol.Name = "Trim the Input columns";
             //Get the path of the data source component
             IDTSPath100 oldpath =  GetPath(dft, ds, true);
             IDTSInput100 endpoint = null;
             IDTSOutput100 startpoint = null;
             IDTSComponentMetaData100 comafter = null;
            
             // if the path exists
             if (oldpath != null)
             {
                 startpoint = oldpath.StartPoint;
                 comafter = oldpath.EndPoint.Component;
                 dft.PathCollection.RemoveObjectByID(oldpath.ID);
                 foreach (IDTSInput100 item in comafter.InputCollection)
                 {
                     if (!item.IsAttached) { endpoint = item; break; }
                 }
                 AddPath(dft, startpoint, TrimDervCol.InputCollection[0]);
                 AddPath(dft, TrimDervCol.OutputCollection[0], endpoint);
             }
             else
             {
                 IDTSPath100 SourceToDerivedPath = dft.PathCollection.New();
                 SourceToDerivedPath.AttachPathAndPropagateNotifications(ds.OutputCollection[0], TrimDervCol.InputCollection[0]);
             }
             TrimDervCol.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
             TrimDervCol.InputCollection[0].HasSideEffects = false;

            IDTSInput100 DerivedColumnInput = TrimDervCol.InputCollection[0];
            IDTSVirtualInput100 DerivedColumnVirtualInput = DerivedColumnInput.GetVirtualInput();
            IDTSVirtualInputColumnCollection100 DerivedColumnVirtualInputColumns = DerivedColumnVirtualInput.VirtualInputColumnCollection;

            foreach (IDTSVirtualInputColumn100 virtualInputColumnDT in DerivedColumnVirtualInputColumns)
            {
                if ( virtualInputColumnDT.DataType.Equals(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_STR)
                  || virtualInputColumnDT.DataType.Equals(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_WSTR)
                  || virtualInputColumnDT.DataType.Equals(Microsoft.SqlServer.Dts.Runtime.Wrapper.DataType.DT_TEXT)
                  )
                {
                    IDTSInputColumn100 inputColumn = DesignDerivedTransformColumns.SetUsageType(DerivedColumnInput.ID, DerivedColumnVirtualInput, virtualInputColumnDT.LineageID, DTSUsageType.UT_READWRITE);
                    #region test
                        //IDTSOutputColumn100 myCol =
                        //  TrimDervCol.OutputCollection[0].OutputColumnCollection.New();
                        //myCol.Name = inputColumn.Name;
                        //myCol.SetDataTypeProperties(inputColumn.DataType,inputColumn.Length,inputColumn.Precision,inputColumn.Scale,inputColumn.CodePage);
                        //myCol.ExternalMetadataColumnID = 0;
                        //myCol.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
                        //myCol.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;

                        //IDTSCustomProperty100 myProp = myCol.CustomPropertyCollection.New();
                        //myProp.Name = "Expression";
                        //myProp.Value = "[TRIM](#" + inputColumn.LineageID + ")";

                        //myProp = myCol.CustomPropertyCollection.New();
                        //myProp.Name = "FriendlyExpression";
                        //myProp.Value = "TRIM(" + inputColumn.Name + ")";
                        #endregion
                    IDTSCustomProperty100 myProp = inputColumn.CustomPropertyCollection["Expression"];
                    myProp.Value = "[TRIM](#" + inputColumn.LineageID + ")";
                    myProp = inputColumn.CustomPropertyCollection["FriendlyExpression"];
                    myProp.Value = "TRIM(" + inputColumn.Name + ")";
                }
            }
            



        }
        #endregion
 
    }

    public class ParaObject
    {
        public string pkgpath { get; set; }
        public bool isAudit { get; set; }
        public StringBuilder sbProcessInfo { set; get; }
        public StringBuilder sbScripts { set; get; }
        public StringBuilder sbSTMT { set; get; }
        public int ErrorCount { set; get; }
        public bool bRemoveFormer { set; get; }

        public ParaObject()
        {
            sbProcessInfo = new StringBuilder();
            sbScripts = new StringBuilder();
            sbSTMT = new StringBuilder();
            isAudit = true;
        }
        public ParaObject(string _pkgpath,bool _isAudit)
        {
            pkgpath = _pkgpath;
            sbProcessInfo = new StringBuilder();
            sbScripts = new StringBuilder();
            sbSTMT = new StringBuilder();
            isAudit = _isAudit;
        }
    }

    class AuditFramework
    {
        private ManualResetEvent _doneEvent;
        private PackageHelper ph = new PackageHelper();
        private ParaObject _po;
        private System.Windows.Forms.RichTextBox _rtb;

        public AuditFramework(ParaObject po, ManualResetEvent doneEvent, System.Windows.Forms.RichTextBox rtb)
        {
             _po = po;
             _doneEvent = doneEvent;
             _rtb = rtb;
        }

        public AuditFramework(ParaObject po, System.Windows.Forms.RichTextBox rtb)
        {
            _po = po;
            _rtb = rtb;
        }
      
        public void ThreadPoolCallback(object obj)
        {
            ph.AddAuditFramework2(_po);
            //var rtb = (System.Windows.Forms.RichTextBox)obj;
            // will be invoked from UI thread
            Action<string> cb = v =>
            {
                _rtb.AppendText(v + Environment.NewLine);
            };
            _rtb.BeginInvoke(cb, _po.sbProcessInfo.ToString());
            _doneEvent.Set();
        }

        public void DoAuditFrameWork()
        {
            ph.AddAuditFramework2(_po);
            Action<string> cb = v =>
            {
                _rtb.AppendText(v + Environment.NewLine);
            };
            _rtb.BeginInvoke(cb, _po.sbProcessInfo.ToString());
        }

        public void DoNotification()
        {
            ph.addNotificationParallel(_po);
            Action<string> cb = v =>
            {
                _rtb.AppendText(v + Environment.NewLine);
            };
            _rtb.BeginInvoke(cb, _po.sbProcessInfo.ToString());
        }

        public void SetLogDB()
        {
            ph.IsDbLog = true;
        }
    }
}


